﻿using Dapper;
using HR.Models;
using HR.IService;
using System.Data;
using System.Data.SqlClient;

namespace HR.Service
{
    public class Major_changeService : IMajor_changeService
    {
        public Major_changeService(HRDBContext db, IConfiguration configuration)
        {
            this.configuration = configuration;
            conStr = configuration["ConnectionStrings"];
            my = db;
        }
        private readonly HRDBContext my;
        //连接字符串 
        string? conStr;
        private readonly IConfiguration configuration;
        //有条件查询
        public async Task<FenYe<human_file>> changeFenYe(major_change_CX change_CX)
        {
            return await Task.Run(() =>
            {
                using (SqlConnection connection = new SqlConnection(conStr))
                {
                    DynamicParameters dynamicParameters = new DynamicParameters();
                    dynamicParameters.Add("@pageSize", change_CX.pageSize);
                    dynamicParameters.Add("@keyName", "huf_id");
                    dynamicParameters.Add("@tabelName", "human_file");
                    string sql1 = $"first_kind_name = '{change_CX.yiji}'";
                    if (change_CX.erji != null)
                    {
                        sql1 += $"and second_kind_name = '{change_CX.erji}'";
                    }
                    if (change_CX.sanji != null)
                    {
                        sql1 += $"and third_kind_name = '{change_CX.sanji}'";
                    }
                    if (change_CX.shijianKS != null && change_CX.shijianJS != null)
                    {
                        sql1 += $"and regist_time >='{change_CX.shijianKS}'and regist_time <='{change_CX.shijianJS}'";
                    }
                    dynamicParameters.Add("@where", sql1); dynamicParameters.Add("@currentPage", change_CX.currentPage);
                    dynamicParameters.Add("@lie", "*");
                    dynamicParameters.Add("@rows", dbType: DbType.Int32, direction: ParameterDirection.Output);
                    string sql = "exec proc_Fenye @pageSize, @keyName, @tabelName, @where, @currentPage, @lie, @rows out";
                    List<human_file> list = connection.Query<human_file>(sql, dynamicParameters).ToList();
                    int rows = dynamicParameters.Get<int>("@rows");
                    FenYe<human_file> fenYe = new FenYe<human_file>()
                    {
                        Rows = rows,
                        Dt = list
                    };

                    return fenYe;
                }
            });
        }
        //薪酬查询，下拉列表框
        public async Task<List<salary_standard>> Select()
        {
            return await Task.Run(() =>
            {
                List<salary_standard> list = my.salary_standards.ToList();
                return list;
            });
        }


        //查询全部
        public async Task<FenYe<human_file>> changeFenYe1(int pageSize, int currentPage)
        {
            return await Task.Run(() =>
            {
                using (SqlConnection connection = new SqlConnection(conStr))
                {
                    DynamicParameters dynamicParameters = new DynamicParameters();
                    dynamicParameters.Add("@pageSize", pageSize);
                    dynamicParameters.Add("@keyName", "huf_id");
                    dynamicParameters.Add("@tabelName", "human_file");
                    dynamicParameters.Add("@where", "");
                    dynamicParameters.Add("@currentPage", currentPage);
                    dynamicParameters.Add("@lie", "*");
                    dynamicParameters.Add("@rows", dbType: DbType.Int32, direction: ParameterDirection.Output);
                    string sql = "exec proc_Fenye @pageSize, @keyName, @tabelName, @where, @currentPage, @lie, @rows out";
                    List<human_file> list = connection.Query<human_file>(sql, dynamicParameters).ToList();
                    int rows = dynamicParameters.Get<int>("@rows");
                    FenYe<human_file> fenYe = new FenYe<human_file>()
                    {
                        Rows = rows,
                        Dt = list
                    };

                    return fenYe;
                }
            });
        }
        //修改
        public async Task<int> major_changeUpdate(major_change change)
        {
            using (SqlConnection connection = new SqlConnection(conStr))
            {
                Task<int> config = Task.Run(() =>
                {
                    return connection.Execute($@"
                     update major_change set check_reason='{change.check_reason}',check_status='{change.check_status}',checker='{change.checker}',
                     check_time='{change.check_time}' where mch_id='{change.mch_id}'");
                });
                return await config;
            }
        }
        //添加
        public async Task<int> major_changeAdd(major_change change)
        {
            using (SqlConnection con = new SqlConnection(conStr))
            {
                Task<int> config = Task.Run(() =>
                {
                    return con.Execute($@"insert into major_change(first_kind_id,first_kind_name,
                    second_kind_id,second_kind_name,third_kind_id,
                    third_kind_name,major_kind_id,major_kind_name,major_id,major_name,salary_standard_id,
                    salary_standard_name,new_first_kind_id,new_first_kind_name,new_second_kind_id,new_second_kind_name,
                    new_third_kind_id,new_third_kind_name,new_major_kind_id,new_major_kind_name,new_major_id,
                    new_major_name,new_salary_standard_id,new_salary_standard_name,new_salary_sum,register,
                    regist_time,human_id,human_name,salary_sum,change_reason,check_status) values('{change.first_kind_id}','{change.first_kind_name}',
                    '{change.second_kind_id}','{change.second_kind_name}','{change.third_kind_id}',
                    '{change.third_kind_name}','{change.major_kind_id}','{change.major_kind_name}',
                    '{change.major_id}','{change.major_name}','{change.salary_standard_id}','{change.salary_standard_name}','{change.new_first_kind_id}',
                    '{change.new_first_kind_name}','{change.new_second_kind_id}','{change.new_second_kind_name}',
                    '{change.new_third_kind_id}','{change.new_third_kind_name}','{change.new_major_kind_id}',
                    '{change.new_major_kind_name}','{change.new_major_id}','{change.new_major_name}',
                    '{change.new_salary_standard_id}','{change.new_salary_standard_name}','{change.new_salary_sum}',
                    '{change.register}','{change.regist_time}','{change.human_id}','{change.human_name}','{change.salary_sum}',
                    '{change.change_reason}','{change.check_status}')");
                });
                return await config;
            }
        }
        //职位审核分页    查询
        public async Task<FenYe<major_change>> changeFenYe2(int pageSize, int currentPage)
        {
            return await Task.Run(() =>
            {
                using (SqlConnection connection = new SqlConnection(conStr))
                {
                    DynamicParameters dynamicParameters = new DynamicParameters();
                    dynamicParameters.Add("@pageSize", pageSize);
                    dynamicParameters.Add("@keyName", "mch_id");
                    dynamicParameters.Add("@tabelName", "major_change");
                    dynamicParameters.Add("@where", "check_status= 0 ");
                    dynamicParameters.Add("@currentPage", currentPage);
                    dynamicParameters.Add("@lie", "*");
                    dynamicParameters.Add("@rows", dbType: DbType.Int32, direction: ParameterDirection.Output);
                    string sql = "exec proc_Fenye @pageSize, @keyName, @tabelName, @where, @currentPage, @lie, @rows out";
                    List<major_change> list = connection.Query<major_change>(sql, dynamicParameters).ToList();
                    int rows = dynamicParameters.Get<int>("@rows");
                    FenYe<major_change> fenYe = new FenYe<major_change>()
                    {
                        Rows = rows,
                        Dt = list
                    };

                    return fenYe;
                }
            });
        }
        //职位调动表调动（有条件查询）
        public async Task<List<major_change>> major_changeSelect(major_change_CX change_CX)
        {
            using (SqlConnection con = new SqlConnection(conStr))
            {
                Task<List<major_change>> config = Task.Run(() =>
                {
                    string sql = $@"select * from major_change 
                                 where new_first_kind_name='{change_CX.yiji}' 
                                 and check_status=1";
                    if (change_CX.erji != null)
                    {
                        sql += $"and new_second_kind_name='{change_CX.erji}'";
                    }
                    if (change_CX.sanji != null)
                    {
                        sql += $"and new_third_kind_name='{change_CX.sanji}'";
                    }
                    if (change_CX.zhiwei1 != null)
                    {
                        sql += $"and new_major_kind_name='{change_CX.zhiwei1}'";
                    }
                    if (change_CX.zhiwei2 != null)
                    {
                        sql += $"and new_major_name='{change_CX.zhiwei2}'";
                    }
                    if (change_CX.shijianKS != null && change_CX.shijianJS != null)
                    {
                        sql += $"and (regist_time >='{change_CX.shijianKS}'and regist_time <='{change_CX.shijianJS}')";
                    }
                    return con.Query<major_change>(sql).ToList();
                });
                return await config;
            }
        }
        //职位调动表调动（查询）
        public async Task<List<major_change>> major_changeSelect1()
        {
            using (SqlConnection con = new SqlConnection(conStr))
            {
                Task<List<major_change>> config = Task.Run(() =>
                {
                    return con.Query<major_change>($@"select * from major_change where check_status=1").ToList();
                });
                return await config;
            }
        }
        //人力资源档案（修改）
        public int human_fileUpdate(human_file file)
        {
            var i = file.major_change_amount + 1;
            using (SqlConnection connection = new SqlConnection(conStr))
            {
                return connection.Execute($@"
                     update human_file set first_kind_id='{file.first_kind_id}',first_kind_name='{file.first_kind_name}',second_kind_id='{file.second_kind_id}',second_kind_name='{file.second_kind_name}',
                     third_kind_id='{file.third_kind_id}',third_kind_name='{file.third_kind_name}',human_major_kind_id='{file.human_major_kind_id}',human_major_kind_name='{file.human_major_kind_name}',
                     human_major_id='{file.human_major_id}',human_major_name='{file.human_major_name}',salary_standard_id='{file.salary_standard_id}',salary_standard_name='{file.salary_standard_name}',
                     salary_sum='{file.salary_sum}',major_change_amount='{i}'
                     where human_id='{file.human_id}'");
            }
        }

        //public List<human_file> human_fileselect(string human_id)
        //{
        //    using (SqlConnection con = new SqlConnection(conStr))
        //    {
        //        string sql = $"select * from human_file where human_id='{human_id}'";
        //        return con.Query<human_file>(sql).ToList();
        //    }

        //}
    }
}
